package com.wen.model;

import com.wen.bean.UserInfoBean;
import com.wen.dao.DbProvider;
import com.wen.uilts.MyUilts;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class UserModel {

    /**
     * 逻辑删除，在数据库增加操作列userDelete字段,并修改所有查询语句的条件，添加userDelete=1
     * @param strId 删除记录的Id
     * @return 删除操作数
     */
    public int logicallyDeleteUserById(String strId){
        //删除语句
        String strSql = "update userInfo set userDelete = 1 where userId = ?";
        //调用dba层进行数据操作
        int i = MyUilts.getNewInstance(DbProvider.class).execute(strSql, strId);
        return i;
    }

    /**
     * 物理删除
     * @param strId 删除记录的Id
     * @return 删除操作数
     */
    public int deleteUserById(String strId){
        //删除语句
        String strSql = "delete from userinfo where userId = ?";
        //调用dba层进行数据操作
        int i = MyUilts.getNewInstance(DbProvider.class).execute(strSql, strId);
        return i;
    }

    /**
     * 查出id对应的一条记录
     *
     * @return 返回UserINfoBean集合
     */
    public UserInfoBean queryUserInfoById(String strId) {
        //创建对象
        UserInfoBean userInfoBean = null;
        //查询语句
        String strSql = "select * from userinfo where userId = ? ";
        userInfoBean = MyUilts.getNewInstance(DbProvider.class).query(strSql, new BeanHandler<>(UserInfoBean.class), strId);
        return userInfoBean;
    }

    /**
     * 按条件查找
     *
     * @param userInfoBean 对象类
     * @return 返回查找集合
     */
    public List<UserInfoBean> queryUserInfoByParams(UserInfoBean userInfoBean) {
        //创建List对象
        List<UserInfoBean> lst = new ArrayList<>();
        // 查询语句---根据查询条件进行动态拼接
        // 定义一个存储占位符参数的集合List
        List lstParams = new ArrayList();
        //sql语句
        /*select * from userinfo where userName LIKE '任%' and phone LIKE '1%' and userDate BETWEEN
        '2021-04-01 00:00:00' AND '2021-04-07 23:59:59' ORDER BY userName DESC;
        1=1指保证固定形式
        */
        //StringBuilder sbSql = new StringBuilder("select * from userinfo where 1=1 ");
        StringBuilder sbSql = new StringBuilder("select * from userinfo where userDelete = 0  ");

        if (userInfoBean != null) {
            // 动态拼接sql语句
            // 用户名
            if (userInfoBean.getUserName() != null && !userInfoBean.getUserName().equals("")) {
                sbSql.append(" AND userName LIKE ? ");
                //拼接占位符参数
                lstParams.add("%" + userInfoBean.getUserName() + "%");
            }
            //联系查询
            //lang3工具包
            if (StringUtils.isNotEmpty(userInfoBean.getPhone())) {
                sbSql.append("AND phone LIKE ? ");
                //拼接占位符参数
                lstParams.add("%" + userInfoBean.getPhone() + "%");
            }
            // 开始日期查找
            if (userInfoBean.getUserDate() != null) {
                sbSql.append("AND userDate BETWEEN ? ");
                //拼接占位符参数
                lstParams.add(DateFormatUtils.format(userInfoBean.getUserDate(), "yyyy-MM-dd HH:mm:ss"));
            }
            // 结束日期查找
            if (userInfoBean.getUserDate2() != null) {
                sbSql.append("AND ? ");
                //拼接占位符参数
                lstParams.add(DateFormatUtils.format(userInfoBean.getUserDate2(), "yyyy-MM-dd HH:mm:ss"));
            }
        }
        //排序
        sbSql.append(" ORDER BY userName DESC");
        //查询操作
        lst = MyUilts.getNewInstance(DbProvider.class).query(sbSql.toString(), new BeanListHandler<>(UserInfoBean.class), lstParams.toArray());
        return lst;
    }

    /**
     * 按姓名查找
     *
     * @return 返回UserINfoBean集合
     */
    public List<UserInfoBean> queryUserInfoName(UserInfoBean userInfoBean) {
        //创建list集合对象
        List<UserInfoBean> list = new ArrayList<>();
        List lstParams = new ArrayList();
//        StringBuilder sbSql = new StringBuilder("select * from userinfo where 1=1");
        StringBuilder sbSql = new StringBuilder("select * from userinfo where userDelete = 0");
        if (userInfoBean != null) {
            // 动态拼接sql语句
            // 用户名
            if (userInfoBean.getUserName() != null && !userInfoBean.getUserName().equals("")) {
                sbSql.append(" AND userName Like ? ");
                //拼接占位符参数
                lstParams.add("%" + userInfoBean.getUserName() + "%");
            }
        }
        //排序
        sbSql.append(" ORDER BY userId");
        //查询操作
        list = MyUilts.getNewInstance(DbProvider.class).query(sbSql.toString(), new BeanListHandler<>(UserInfoBean.class), lstParams.toArray());
        return list;
    }

    /**
     * 查出所有人
     *
     * @return 返回UserINfoBean集合
     */
    public List<UserInfoBean> queryUserInfoAll() {
        //创建list集合对象
        List<UserInfoBean> list = new ArrayList<>();
        //查询语句
        String strSql = "select * from userinfo where userDelete = 0 order by userId";
        list = MyUilts.getNewInstance(DbProvider.class).query(strSql, new BeanListHandler<>(UserInfoBean.class));
        return list;
    }

    /**
     * 添加用户
     *
     * @param userInfoBean 用户类型
     * @return 返回变量
     */
    public int addUserInfo(UserInfoBean userInfoBean) {
        //默认返回值
        int i = 0;
        /*
        这个方法，既可以处理新增业务逻辑，也可以处理修改业务逻辑：
        新增和修改的区别：
            新增时参数实体bean userinfobean 的主键字段的值 为null;
            修改时参数实体bean userinfobean 的主键字段的值 不为null;
         */
        if (userInfoBean.getUserId() != null) {
            // 主键字段不为null ，进行修改操作
            // 修改操作的sql语句
            String strSql = "update userinfo set userName = ? ,userPsw = ? , phone = ? ,userDate = ? where userId = ?";
            i = MyUilts.getNewInstance(DbProvider.class).execute(strSql,
                    new Object[]{
                            userInfoBean.getUserName(),
                            userInfoBean.getUserPsw(), userInfoBean.getPhone(),
                            MyUilts.dateConvertStr(userInfoBean.getUserDate(), "yyyy-MM-dd HH:mm:ss"),
                            userInfoBean.getUserId()
                    });
        } else {
            // 主键字段为null，则进行新增操作
            //数据保存的sql语句
            String strSql = "insert into userinfo(userName,userPsw,phone,userDate) values(?,?,?,?)";
            i = MyUilts.getNewInstance(DbProvider.class).execute(strSql,
                    new Object[]{
                            userInfoBean.getUserName(),
                            userInfoBean.getUserPsw(), userInfoBean.getPhone(), MyUilts.dateConvertStr(userInfoBean.getUserDate(), "yyyy-MM-dd HH:mm:ss")
            });
        }
        return i;
    }

    /**
     * 验证用户是否登录成功
     *
     * @param userName 用户名
     * @param userPsw  密码
     * @return 验证结果
     */
    public boolean doLogin(String userName, String userPsw) {
        //默认返回值
        boolean canLogin = false;
        //查询语句
        String strSql = "select * from userInfo where userDelete = 0 AND username = ? and userpsw = ?";
        //创建查询器
        // DbProvider dbProvider = new DbProvider();
        //判断查询是否成功
        //List<Map<String,Object>> list = dbProvider.requryListMap(strSql, userName,userPsw);
        List<Map<String, Object>> list = MyUilts.getNewInstance(DbProvider.class).query(strSql, new MapListHandler(), userName, userPsw);
        if (list != null && list.size() > 0) {
            canLogin = true;
        }
        return canLogin;
    }


}
